import sys
from utils.http import *
from utils.errors import *
from utils.config import *
from utils.process import *
import os
import json
import subprocess
import threading
from pymysql.cursors import DictCursor
from django.shortcuts import render
from datetime import datetime
from jqdatasdk import *
import pandas as pd

auth ('18687455266', '455266')
db = connect_mysql_db ()
pdf2htmlEX = f"docker run --rm  -v `pwd`:/pdf -v {config.crawler_dir}report_file:/pdf/report_file  pdf2htmlex/pdf2htmlex:0.18.8.rc2-master-20200820-ubuntu-20.04-x86_64"
graph = connect_neo4j()

@HttpWrapper
def test(request):
    return "Connection Success!"


@HttpWrapper
def search_secCode(request):
    searchString = request.GET.get ("searchString")
    if searchString.isnumeric():
        sql = f"""select * from secCodes s left join company_info c on s.security = c.code where secCode = {searchString}"""
    else:
        sql = f"""select * from secCodes s left join company_info c on s.security = c.code where secName like "%{searchString}%" """
    # todo:需要解决sql注入问题
    with db.cursor (DictCursor) as cursor:
        cursor.execute (sql)
        result = cursor.fetchall ()
    result = [{k:str(v) for k,v in o.items()} for o in result]
    if len (result) == 0:
        raise NoValue ()
    return result


def get_report_by_secCode(request):
    announcementId = request.GET.get ("announcementId")
    if os.path.exists (f"processed_report/{announcementId}"):
        return render (request, f"{announcementId}/{announcementId}.html")
    else:
        with db.cursor (DictCursor) as cursor:
            cursor.execute (f""" select isSaved,savedPath from reportInfo where announcementId='{announcementId}' """)
            result = cursor.fetchall ()
            if len (result) == 0:
                raise NoValue ()
            if result[0]['isSaved'] == 0:
                raise NoValue ()
            source_path = result[0]['savedPath']
            # source_path = config.crawler_dir + savedPath[2:]
            print (os.system ("pwd"))
            os.system (
                f"{pdf2htmlEX} --zoom 1.3 --embed o --outline-filename outline.html --dest-dir processed_report/{announcementId}/ {source_path}")
            if os.path.exists (f"processed_report/{announcementId}"):
                return render (request, f"{announcementId}/{announcementId}.html")
            return HttpResponse (json.dumps ({'code': 0, 'message': "No file exist"}))


@HttpWrapper
def get_all_sec_info(request):
    start = request.GET.get ("start")
    offset = request.GET.get ("offset")
    with db.cursor (DictCursor) as cursor:
        cursor.execute (f""" select * from secCodes order by secCode asc limit {start},{offset} """)
        res = cursor.fetchall ()
        securities = [ob['security'] for ob in res]
        infos = {'infos': []}
        for code in securities:
            # 缓存信息
            cursor.execute (f""" select * from company_info where code='{code}' """)
            res = cursor.fetchall ()
            if len (res) == 0:
                q = query (finance.STK_COMPANY_INFO).filter (finance.STK_COMPANY_INFO.code == code)
                res = finance.run_query (q).iloc[0, :]
                cols = ['id', 'code', 'full_name', 'short_name', 'a_code', 'b_code', 'h_code', 'register_location',
                           'register_capital',
                           'currency', 'website', 'email', 'contact_number', 'main_business', 'business_scope',
                           'description',
                           'province', 'city', 'industry_id', 'industry_1', 'industry_2', 'cpafirm', 'lawfirm']
                res = res[cols]
                res = res.to_dict ()
                try:
                    cursor.executemany(f""" insert into company_info ({','.join(cols)}) values ({','.join(['%s']*len(cols))})""",[tuple(res.values())])
                    db.commit()
                except Exception as e:
                    db.rollback()
                    print(e)
                    raise NoValue()
            else:
                res = res[0]
            res = {i: str (v) for i, v in res.items ()}
            infos['infos'].append (res)
    return infos


@HttpWrapper
def get_sec_info(request):
    code = request.GET.get ("security")
    with db.cursor (DictCursor) as cursor:
        # 缓存信息
        cursor.execute (f""" select * from company_info where code='{code}' """)
        res = cursor.fetchall ()
        if len (res) == 0:
            q = query (finance.STK_COMPANY_INFO).filter (finance.STK_COMPANY_INFO.code == code)
            res = finance.run_query (q).iloc[0, :]
            cols = ['id', 'code', 'full_name', 'short_name', 'a_code', 'b_code', 'h_code', 'register_location',
                       'register_capital',
                       'currency', 'website', 'email', 'contact_number', 'main_business', 'business_scope',
                       'description',
                       'province', 'city', 'industry_id', 'industry_1', 'industry_2', 'cpafirm', 'lawfirm']
            res = res[cols]
            res = res.to_dict ()
            try:
                cursor.executemany(f""" insert into company_info ({','.join(cols)}) values ({','.join(['%s']*len(cols))})""",[tuple(res.values())])
                db.commit()
            except Exception as e:
                db.rollback()
                print(e)
                raise NoValue()
        else:
            res = res[0]
        res = {i: str (v) for i, v in res.items ()}
    return res

@HttpWrapper
def getRecentReportAnnouncementId(request):
    secCode = request.GET.get('secCode')
    with db.cursor(DictCursor) as cursor:
        cursor.execute(f""" select announcementId from reportInfo where secCode={secCode} order by announcementTime desc limit 1""")
        res = cursor.fetchone()
    return res['announcementId']

@HttpWrapper
def getAllRecentReportAnnouncementInfo(request):
    secCode = request.GET.get('secCode')
    with db.cursor(DictCursor) as cursor:
        cursor.execute(f""" select announcementId,announcementTime,announcementTitle,secCodes.secCode as secCode,security from reportInfo left join secCodes on secCodes.secCode=reportInfo.secCode where secCodes.secCode={secCode} order by announcementTime desc""")
        res = cursor.fetchall()
    for o in res:
        tmp = datetime.fromtimestamp(int(int(o['announcementTime'])/1000))
        o['announcementTime'] = tmp.strftime("%Y-%m-%d")
    return res

def test_template(request):
    return render(request,"test/test.html")

@HttpWrapper
def test_js(request):
    with open('processed_report/test/testTemplate.js','r') as f:
        a = f.read()
    return a

@HttpWrapper
def report_template_js(request):
    with open('templates/reportJs.js','r') as f:
        a = f.read()
    return a

@HttpWrapper
def getReportInfo(request):
    announcementId = request.GET.get("announcementId")
    with db.cursor(DictCursor) as cursor:
        cursor.execute(f""" select announcementTime,announcementTitle from reportInfo where announcementId = {announcementId} """)
        res = cursor.fetchone()
    tmp = datetime.fromtimestamp(int(int(res['announcementTime'])/1000))
    res['announcementTime'] = tmp.strftime("%Y-%m-%d")
    return res

@HttpWrapper
def getAnnouncementOutline(request):
    announcementId = request.GET.get ("announcementId")
    if os.path.exists(f"processed_report/{announcementId}/outline.html"):
        with open(f"processed_report/{announcementId}/outline.html",'r') as f:
            data = f.read()
        return data
    else:
        raise NoValue()


def parse_res(res,dest_name):
    nodes = []
    id_dict = {}
    links = []
    rootId = 0
    nodeColor = {}
    for i, (n, m, r) in res.iterrows():
        try:
            id_dict[n.identity] = n['name']
            id_dict[m.identity] = m['name']
            r_name = str(type(r).__name__)
            tmp_link = {"from": str (n.identity), "to": str (m.identity), "text":r_name,"data": { 'type': r_name }}
            if r_name == "长期投资":
                tmp_link["color"] = "#FF3066"
                nodeColor[str (m.identity)] = "#FF3066"
            elif r_name == "被审计":
                tmp_link["color"] = "#382AFF"
                nodeColor[str (m.identity)] = "#382AFF"
            elif r_name == "法律咨询":
                tmp_link["color"] = "#FFB206"
                nodeColor[str (m.identity)] = "#FFB206"
            links.append (tmp_link)
            if n['name'] == dest_name:
                rootId = n.identity
            if m['name'] == dest_name:
                rootId = m.identity
        except:
            pass
    for i, v in id_dict.items ():
        tmp_n = {"id": str (i), "text": v}
        if str(i) in nodeColor.keys():
            tmp_n['color'] = nodeColor[str(i)]
        nodes.append (tmp_n)
    result = {
        'nodes': nodes,
        'rootId': str (rootId),
        'links': links
    }
    return json.dumps (result)

@HttpWrapper
def get_query_relation_data(request):
    query_type = request.GET.get ("queryType") # 0或1  0为获取所有  1为获取直接相关
    relations = ['被持有', '长期投资', '被审计', '法律咨询']
    labels = ['holder', 'cpa', 'law']
    dest_name = request.GET.get ("destName")
    dest_name = dest_name.encode('utf-8').decode('utf-8')

    if query_type == '0': # 查询所有  限制数量
        limit = request.GET.get ("limit")
        res = graph.run (
            f"""match p= ( (n{{name:"{dest_name}" }})-[r]-(m) )return n,m,r limit {limit}""")
        res = pd.DataFrame (res)
        js = parse_res (res,dest_name)
        return js
    elif query_type == '1': # 查询部分
        try:
            relation = int(request.GET.get ("relation"))
            relation = relations[relation]
            res = graph.run (
                f"""match(n)-[r:{relation}]->(m) 
                        where n.name= "{dest_name}" or m.name= "{dest_name}"
                        return n,m,r;
                """)

            js = parse_res (res,dest_name)
            return js
        except:
            raise NoValue()
    else:
        raise NoValue()